package henu.dao.impl;

import henu.bean.Car;
import henu.bean.Insurance;
import henu.bean.InsuranceCarPInformation;
import henu.dao.InsuranceDao;
import henu.util.Dbcp;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

public class InsuranceDaoImpl implements InsuranceDao {

	public int findByuerId(String userid){
		Car car= null;
		String sql="SELECT * FROM CARINFORMATION WHERE userid=?";
		QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
		if(userid!=null)
			{
				try {
					car=qr.query(sql,new BeanHandler<Car>(Car.class), userid);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		else 
			return 0;
		return car.getCarid();
	}
	@Override
	public int add(Insurance insurance,String userid) {
			// TODO 自动生成的方法存根
		int carid=findByuerId(userid);
			int result=0;
			String sql="insert into INSURANCE (status,carid,enterdate,ino,insurancefile,insuranceend,insurancestart,memo,tno,trafficend,trafficfile,trafficstart,updatedate,vehiclelicense) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
			if(carid>0)
			{
				Object[] params ={0,carid,insurance.getEnterdate(),insurance.getIno(),insurance.getInsurancefile(),insurance.getInsuranceend(),insurance.getInsurancestart(),insurance.getMemo(),insurance.getTno(),insurance.getTrafficend(),insurance.getTrafficfile(),insurance.getTrafficstart(),insurance.getUpdatedate(),insurance.getVehiclelicense()};
				try {
					result =qr.update(sql, params);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				else
					return 0;
			return result;
	}

	@Override
	public int deleteById(int inid) {
		
		int result = 0;
		String sql ="DELETE FROM INSURANCE WHERE inid=?";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		try {
			result =qr .update(sql, inid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int update(int inid,Insurance insurance) {
		
		int result = 0;
		String sql="UPDATE INSURANCE SET carid=?,enterdate=?,ino=?,insurancefile=?,insuranceend=?,insurancestart=?,memo=?,tno=?,trafficend=?,trafficfile=?,trafficstart=?,updatedate=?,vehiclelicense=? WHERE inid=?";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		Object[] params = {insurance.getCarid(),insurance.getEnterdate(),insurance.getIno(),insurance.getInsurancefile(),insurance.getInsuranceend(),insurance.getInsurancestart(),insurance.getMemo(),insurance.getTno(),insurance.getTrafficend(),insurance.getTrafficfile(),insurance.getTrafficstart(),insurance.getUpdatedate(),insurance.getVehiclelicense(),inid};
		try {
			result = qr.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public List<Insurance> findAll() {

		String sql="SELECT * FROM INSURANCE";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		List<Insurance> list = null;
		try {
			list = qr.query(sql, new BeanListHandler<Insurance>(Insurance.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public Insurance findById(int inid){
		Insurance insurance=null;
		String sql="SELECT * FROM INSURANCE WHERE INID=?";
		QueryRunner runner =new QueryRunner(Dbcp.getDataSource());
		try {
			insurance=runner.query(sql,new BeanHandler<Insurance>(Insurance.class),inid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return insurance;
	}
	@Override
	public List<InsuranceCarPInformation> findByProperty(String property, String key,String comid, String sort, int start, int end) {
		List<InsuranceCarPInformation> list =null;	
		String sql = "SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (SELECT * FROM VIEW_INSURANCE_CAR_PINFO where comid=? and "+property+"='"+key+"' order by regTime "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			
			list =runner.query(sql, new BeanListHandler<InsuranceCarPInformation>(InsuranceCarPInformation.class),comid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			/*System.out.println(list);
			System.out.println(sql);*/
		return list;
	}

	public InsuranceCarPInformation findByIdCard(String idCard){
		InsuranceCarPInformation icp =null;
		String sql="SELECT * FROM VIEW_INSURANCE_CAR_PINFO where idCard=?";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		try {
			icp =qr.query(sql,new BeanHandler<InsuranceCarPInformation>(InsuranceCarPInformation.class),idCard);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return icp;
		
	}
	/*public static void main(String[] args)
	{
		InsuranceDaoImpl h =new InsuranceDaoImpl();
		h.findByProperty("realName", "刘涛", "11", "", 1, 100);
	}*/
	
	@Override
	public Car findvehiclebyidcard(String idcard) {
		//通过idcard查找，并返回车辆的车牌号
		Car car=null;
		String sql="SELECT * FROM VIEW_INSURANCE_CAR_PINFO where idCard=?";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		try {
			car =qr.query(sql,new BeanHandler<Car>(Car.class),idcard);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return car;
	}
	@Override//前台查找保险要先通过身份证找到carid所对应的所有车辆
	public List<Car> findAllCar(String idcard) {
		List<Car> carlist = null;
		String sql="SELECT * FROM CARINFORMATION where USERID=?";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		try {
			carlist =qr.query(sql,new BeanListHandler<Car>(Car.class),idcard);
			//System.out.println(carlist);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return carlist;
	}
	@Override//通过Carid查找单个用户每个车的保险
	public Insurance findByCarid(int carid) {
		Insurance list = null;
		String sql="SELECT * FROM INSURANCE WHERE CARID=?";
		QueryRunner runner =new QueryRunner(Dbcp.getDataSource());
		try {
			list=runner.query(sql,new BeanHandler<Insurance>(Insurance.class),carid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
}
